package com.bot.adms.dao.impl;

import com.bot.adms.dao.IAdInfoDao;
import com.bot.adms.model.entity.AdInfo;
import com.bot.adms.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 广告信息表持久层接口实现
 *
 * @author ZHOUTAO
 */
public class AdInfoDaoImpl implements IAdInfoDao {
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;

    /**
     * 添加广告
     *
     * @param adInfo 广告信息,包含广告id，广告标题，广告状态等等
     * @return Integer 添加的条数
     */
    @Override
    public Integer insertAdInfo(AdInfo adInfo) {
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String createTimeFmt = dateFormat.format(adInfo.getCreateTime());
            String sql = "INSERT INTO `bo_adms`.`ad_info` (`ad_title`, `sp_sc_type`, `lay_style`, `status`, `create_time`, `apply_id`) VALUES ('" + adInfo.getAdTitle() + "', " + adInfo.getSpScType() + ", " + adInfo.getLayStyle() + ", " + adInfo.getStatus() + ", '" + createTimeFmt + "', " + adInfo.getApplyId() + ");";
            //4.执行SQL语句
            int count = stmt.executeUpdate(sql);
            //5.返回执行条数
            return count;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }

    }

    /**
     * 修改广告
     *
     * @param adInfo 广告信息,包含广告id，广告标题，广告状态等等
     * @return Integer 修改的条数
     */
    @Override
    public Integer updateAdInfo(AdInfo adInfo) {
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String createTimeFmt = dateFormat.format(adInfo.getCreateTime());
            String sql = "UPDATE `bo_adms`.`ad_info` SET `ad_title` = '"+adInfo.getAdTitle()+"', `sp_sc_type` = "+adInfo.getSpScType()+", `lay_style` = "+adInfo.getLayStyle()+", `status` = "+adInfo.getStatus()+", `create_time` = '"+createTimeFmt+"' WHERE `id` = "+adInfo.getId()+";";
            //4.执行SQL语句
            int count = stmt.executeUpdate(sql);
            //5.返回执行条数
            return count;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
    }

    /**
     * 删除广告
     *
     * @param id 广告id
     * @return Integer 删除的条数
     */
    @Override
    public Integer deleteAdInfo(Long id) {
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            String sql = "DELETE FROM `bo_adms`.`ad_info` WHERE `id` = "+id+";";
            //4.执行SQL语句
            int count = stmt.executeUpdate(sql);
            //5.返回执行条数
            return count;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
    }

    /**
     * 查找广告信息
     *
     * @param id 广告id
     * @return AdInfo 广告信息,包含广告id，广告标题，广告状态等等
     */
    @Override
    public AdInfo getAdInfoById(Long id) {
        AdInfo adInfo = null;
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            String sql = "SELECT * FROM `bo_adms`.`ad_info` WHERE `id` = "+id+";";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                 adInfo = new AdInfo();
                 adInfo.setId(rs.getLong("id"));
                 adInfo.setAdTitle(rs.getString("ad_title"));
                 adInfo.setSpScType(rs.getInt("sp_sc_type"));
                 adInfo.setLayStyle(rs.getInt("lay_style"));
                 adInfo.setStatus(rs.getInt("status"));
                 adInfo.setCreateTime(rs.getTimestamp("create_time"));
                 adInfo.setApplyId(rs.getLong("apply_id"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtils.close(conn, stmt,rs);
        }
        return adInfo;
    }

    /**
     * 查询广告信息
     *
     * @param adInfo 广告信息,包含广告id，广告标题，广告状态等等
     * @return List<AdInfo> 广告信息集合,包含广告id，广告标题，广告状态等等
     */
    @Override
    public List<AdInfo> searchAdInfo(AdInfo adInfo) {
        List<AdInfo> adInfoList = new ArrayList<AdInfo>();
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            String sql = "SELECT * FROM `bo_adms`.`ad_info` WHERE 1=1";
            if (adInfo.getId() != null) {
                sql += " AND `id` = " + adInfo.getId();
            }
            if (adInfo.getAdTitle() != null && !"".equals(adInfo.getAdTitle())) {
                sql = sql + " AND ad_title LIKE '%"+adInfo.getAdTitle()+"%'";
            }
            if (adInfo.getStatus() != null) {
                sql = sql + " AND status = " + adInfo.getStatus();
            }
            if(adInfo.getApplyId()!=null){
                sql = sql + " AND apply_id =" + adInfo.getApplyId();
            }
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                AdInfo adInfo1 = new AdInfo();
                adInfo1.setId(rs.getLong("id"));
                adInfo1.setAdTitle(rs.getString("ad_title"));
                adInfo1.setSpScType(rs.getInt("sp_sc_type"));
                adInfo1.setLayStyle(rs.getInt("lay_style"));
                adInfo1.setStatus(rs.getInt("status"));
                adInfo1.setCreateTime(rs.getTimestamp("create_time"));
                adInfo1.setApplyId(rs.getLong("apply_id"));
                adInfoList.add(adInfo1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtils.close(conn, stmt,rs);
        }
        return adInfoList;
    }
}
